"""
编程实现：使用openpyxl在excel中写入10列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中。
CREATE TABLE `user_info` (
  `id` int(11) ,
  `idcard` varchar(50) ,
  `username` varchar(50) ,
  `realname` varchar(50) ,
  `pwd` varchar(50) ,
  `telphone` varchar(12) ,
  `email` varchar(100) ,
  `age` int(11) ,
  `sex` varchar(20) ,
  `address` varchar(200) ,
  `hiredate` date ,
  `sal` double(9,2) ,
  `job` varchar(100) ,
  `company` varchar(100)
) ;
pip install faker -i http://mirrors.aliyun.com/pypi/simple/ --trusted-host pypi.douban.com

"""
import random
from faker import Faker
from openpyxl import Workbook,load_workbook
import pymysql
import copy

fake = Faker('zh_CN')  # 使用中文生成器
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'test_db',
    'charset': 'utf8mb4'
}
excel_file_path = 'user_info.xlsx'
def generate_random_data(n=1000):
    wb = Workbook(excel_file_path)
    ws = wb.create_sheet(title='user_info')  # 获取活动的工作表
    head=['id', 'idcard','username','realname','pwd','telphone','email','age','sex','address','hiredate','sal','job','company']
    ws.append(head)
    for i in range(n):
        # 生成随机数据
        data = [
            i,
            str(random.randint(10000000, 99999999)).zfill(8),
            ''.join(random.choices('abcdefghijklmnopqrstuvwxyz', k=random.randint(4, 8))),
            ''.join(random.choices(''.join([chr(i) for i in range(19968, 40908)]), k=random.randint(2, 3))),
            ''.join(random.choices('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', k=8)),
            fake.phone_number()[1:].replace(' ', ''),
            fake.email(),
            random.randint(22, 30),
            random.choice(['男', '女']),
            fake.city(),
            fake.date_of_birth(tzinfo=None, minimum_age=18, maximum_age=35).strftime('%Y-%m-%d'),
            round(random.randint(3000, 8000)),
            random.choice(['前端开发', '后端开发', '产品经理', '测试工程师', 'UI设计师', '运维工程师']),
            ''.join(random.choices(''.join([chr(i) for i in range(19968, 40908)]), k=random.randint(2, 4))) + '科技有限公司'
        ]
        ws.append(copy.deepcopy(data))
    wb.save(excel_file_path)

def init_table():
    connection = pymysql.connect(**db_config)
    cursor = connection.cursor()
    sql="""CREATE TABLE `user_info` (
          `id` int(11) ,
          `idcard` varchar(50) ,
          `username` varchar(50) ,
          `realname` varchar(50) ,
          `pwd` varchar(50) ,
          `telphone` varchar(12) ,
          `email` varchar(100) ,
          `age` int(11) ,
          `sex` varchar(20) ,
          `address` varchar(200) ,
          `hiredate` date ,
          `sal` double(9,2) ,
          `job` varchar(100) ,
          `company` varchar(100) 
        ) ;"""
    sql=sql.replace("\n", "")
    connection = pymysql.connect(**db_config)
    cursor = connection.cursor()
    cursor.execute(sql)
    connection.commit()
    cursor.close()
    connection.close()

def insert_teble():
    wb = load_workbook(excel_file_path)
    sheet = wb.active

    # 读取数据，跳过标题行
    data = []
    for row in sheet.iter_rows(min_row=2, values_only=True):
        data.append(row)

    # 创建数据库连接
    connection = pymysql.connect(**db_config)
    cursor = connection.cursor()
    insert_sql = """
       INSERT INTO user_info (id, idcard, username, realname, pwd, telphone, email, age, sex, address, hiredate, sal, job, company)
       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
       """
    insert_sql=insert_sql.replace("\n", "")
    try:
        cursor.executemany(insert_sql, data)
        connection.commit()
        print("数据插入成功")
    except pymysql.Error as e:
        connection.rollback()
        print("数据插入失败", e)
    finally:
        # 关闭游标和连接
        cursor.close()
        connection.close()
if __name__ == '__main__':
    # generate_random_data()
    # init_table()
    insert_teble()


